from tkinter import *
from tkinter import ttk
from tkinter.messagebox import *
import pymysql


def connects():  # 打开sql连接
    conn_01 = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="123456", db="mytest", charset="utf8")
    return conn_01


def values():
    conn = connects()
    cursor = conn.cursor()
    sql = f"select cname from course"
    cursor.execute(sql)
    date = cursor.fetchall()
    conn.commit()
    cursor.close()
    return date


def executeSQL(sql):  # 进行sql增删改查的方法
    try:
        conn = connects()
        cursor = conn.cursor()
        cursor.execute(sql)
        conn.commit()
        cursor.close()
        return "ok"
    except:
        return "err"


def showData():
    conn = connects()
    cursor = conn.cursor()
    sql = "select * from user_info"
    cursor.execute(sql)
    data = cursor.fetchall()
    conn.commit()
    cursor.close()
    return data


def shows():
    k = 0
    for data in showData():
        tree.insert("", k, values=(data[0], data[1], data[2], data[3], data[4], data[5]))
        k += 1


tuples = ["账号", "密码", "年龄", "性别", "爱好", "专业"]


def clears():  # 清空
    name_txt.set("")
    pwd_txt.set("")
    age_txt.set("")
    sex_txt.set("女")
    hob_txt.set("")
    cob.current(0)


def trClick(event):
    # 获取选中的内容
    for item in tree.selection():
        itemTxt = tree.item(item, "values")
        # showinfo("数据", message=itemTxt)
        # 给文本框赋值
        name_txt.set(itemTxt[0])
        pwd_txt.set(itemTxt[1])
        age_txt.set(itemTxt[2])
        sex_txt.set(itemTxt[3])
        hob_txt.set(itemTxt[4])
        cob.set(itemTxt[5])


def exits():  # 关闭sql连接并退出程序
    conn = connects()
    conn.close()
    win.destroy()


# 清空界面
def clearTree(tree):
    x = tree.get_children() # 获取到tree的内容
    for i in x:
        tree.delete(i) # 删除数据
# 删除
def remove():
    name = name_txt.get()
    if name != None:
        name = name_txt.get()
        sql = f"delete from user_info where name='{name}'"

        re = executeSQL(sql)
        if re == "ok":
            showinfo("提示", message="删除成功")
            clears()
            clearTree(tree) # 刷新之间先清空界面
            shows() # 重新加载
        else:
            showerror("提示", message="删除失败")
    else:
        showerror("提示", message="请选中一行")


def insert():  # 添加数据
    name = name_txt.get()
    pwd = pwd_txt.get()
    age = age_txt.get()
    sex = sex_txt.get()
    hob = hob_txt.get()
    cls = cob.get()
    sql = f"insert into user_info values ('{name}','{pwd}','{age}','{sex}','{hob}','{cls}')"
    re = executeSQL(sql)
    if re == "ok":
        showinfo("提示", message="添加成功")
        clears()
        clearTree(tree)  # 刷新之间先清空界面
        shows()  # 重新加载
    else:
        showerror("提示", message="添加失败")


def update():  # 修改·数据
    if name_txt.get() != None:
        name = name_txt.get()
        pwd = pwd_txt.get()
        age = age_txt.get()
        sex = sex_txt.get()
        hob = hob_txt.get()
        cls = cob.get()
        sql = f"update user_info set pwd='{pwd}',age='{age}',sex='{sex}',hob='{hob}',class='{cls}'where name='{name}'"
        re = executeSQL(sql)
        if re == "ok":
            showinfo("提示", message="修改成功")
            clears()
            clearTree(tree)  # 刷新之间先清空界面
            shows()  # 重新加载
        else:
            showerror("提示", message="修改失败")
    else:
        showerror("提示", message="请选中一行数据")


win = Tk()
win.title("显示数据")
win.geometry("800x600+600+300")

# 添加滚动条容器
frame = Frame(win, height=260)
frame.pack(expand=NO, fill=X)
# 显示数据
tree = ttk.Treeview(frame, show="headings")  # show="headings" 去除第一空白列
tree["columns"] = tuples

for i in tuples:
    tree.column(i, width=120, anchor="center")
    tree.heading(i, text=i)  # 列头
shows()

tree.bind("<ButtonRelease-1>", trClick)  # 鼠标左键选中文本框并弹出选中的内容
tree.pack()
# 显示滚动条
srcoll = Scrollbar(frame, orient="vertical", command=tree.yview)
# 设置滚动条定位
srcoll.place(relx=0.971, rely=0.028, relwidth=0.020, relheight=0.950)
tree.config(yscrollcommand=srcoll.set)
# 控件
toolbar = Frame(win, height=260)
toolbar.pack(expand=NO, fill=X)
# 账号
Label(toolbar, text="账号:", font=("宋体", 18)).grid(row=0, column=0)
name_txt = StringVar()
Entry(toolbar, textvariable=name_txt, font=("宋体", 18)).grid(row=0, column=1)
# 密码
Label(toolbar, text="密码:", font=("宋体", 18)).grid(row=0, column=2)
pwd_txt = StringVar()
Entry(toolbar, textvariable=pwd_txt, font=("宋体", 18)).grid(row=0, column=3)
# 年龄
Label(toolbar, text="年龄:", font=("宋体", 18)).grid(row=1, column=0)
age_txt = StringVar()
Entry(toolbar, textvariable=age_txt, font=("宋体", 18)).grid(row=1, column=1)
# 性别
Label(toolbar, text="性别:", font=("宋体", 18)).grid(row=1, column=2)
sex_txt = StringVar()
sex_txt.set("女")
Radiobutton(win, text="男", value="男", variable=sex_txt, font=("宋体", 15)).place(x=370, y=256)
Radiobutton(win, text="女", value="女", variable=sex_txt, font=("宋体", 15)).place(x=420, y=256)
# 爱好
Label(toolbar, text="爱好:", font=("宋体", 18)).grid(row=2, column=0)
hob_txt = StringVar()
Entry(toolbar, textvariable=hob_txt, font=("宋体", 18)).grid(row=2, column=1)
# 专业
Label(toolbar, text="专业:", font=("宋体", 18)).grid(row=2, column=2)
cob = ttk.Combobox(toolbar, font=("宋体", 18), state="readonly")
cob['values'] = values()
cob.current(0)  # 默认选中第一个
cob.grid(row=2, column=3)

Button(toolbar, text="添加", font=("宋体", 18), command=insert).grid(row=3, column=0)
Button(toolbar, text="修改", font=("宋体", 18), command=update).grid(row=3, column=1)
Button(toolbar, text="删除", font=("宋体", 18), command=remove).grid(row=3, column=2)
Button(toolbar, text="清空", font=("宋体", 18), command=clears).grid(row=3, column=3)
Button(toolbar, text="关闭", font=("宋体", 18), command=exits).grid(row=3, column=4)

win.mainloop()
